using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;

namespace SharePointUtility
{
    public class DocumentMigration
    {
        DocumentLibrary documentLibrary = new DocumentLibrary();


        public void UploadToSharepoint(int numberOfProtocols, string year, string moduleType)
        {

            try
            {

                using (SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["SQLDataSource"]))
                {

                    connection.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = connection;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = ConfigurationManager.AppSettings["StoredProcName"];
                    SqlParameter pNumberOfProtocols = new SqlParameter("@NUMBEROFPROTOCOLS", DbType.Int32);
                    pNumberOfProtocols.Value = numberOfProtocols;
                    cmd.Parameters.Add(pNumberOfProtocols);

                    SqlParameter pYear = new SqlParameter("@YEAR", DbType.String);
                    pYear.Value = year;
                    cmd.Parameters.Add(pYear);


                    SqlParameter pModuleType = new SqlParameter("@MODULETYPE", DbType.String);
                    pModuleType.Value = moduleType;
                    cmd.Parameters.Add(pModuleType);
                    DataSet ds = new DataSet();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);

                    da.Fill(ds);

                    int protocolId = 0;// Convert.ToInt32(ds.Tables[0].Rows[0]["ProtocolId"]);
                    int newProtocolId;
                    string documentLibraryName = ConfigurationManager.AppSettings["DocumentLibrary"].ToString();

                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        UploadAndHandleException(dr, connection ,  documentLibraryName);

                        newProtocolId = Convert.ToInt32(dr["ProtocolId"]);
                        if (protocolId != newProtocolId)
                        {
                            protocolId = newProtocolId;
                            if (!DBNull.Value.Equals(dr["PROTOCOLNUMBER"]))
                            {
                                UpdateProtocolProperties(dr,  documentLibraryName);
                            }
                        }

                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;

            }
            finally
            {

            }
        }

        /// <summary>
        /// Upload file and in case of exception catch it and carry on
        /// </summary>
        /// <param name="dr"></param>
        /// <param name="connection"></param>
        public void UploadAndHandleException(DataRow dr, SqlConnection connection, string documentLibraryName)
        {

            bool bIsConvertedToPDF = false;
            bool bUploadedToSharePoint = false;
            int docID = Convert.ToInt32(dr["DOCUMENTID"]);
            try
            {
                //will be need for List.asmx
                string baseUrl = string.Empty;
                //will be needed for creating folder 
                string documentLibraryUrl = string.Empty;
                string folderUrl = string.Empty;
                //Actual FILE UPLOAD
                string fileName = dr["Name"].ToString();
                string filExtension = System.IO.Path.GetExtension(fileName);
                if (filExtension != "" && !DBNull.Value.Equals( dr["blob"] ))
                {
                    //Get url like http://insightdocuments.partners.org/animals/1998/
                    baseUrl = GetURL(dr);
                    byte[] blob = (byte[])dr["blob"];
                   //ProtcolId/documentid .txt
                    folderUrl = dr["PROTOCOLID"].ToString() + "/" + docID.ToString() + filExtension;
                    documentLibrary.SaveFileToSharePoint(folderUrl, blob, baseUrl + "/" + documentLibraryName);
                    bUploadedToSharePoint = true;

                    string fullUrl = baseUrl + "/" + documentLibraryName + "/" + folderUrl;

                    UpdateDocumentProperties(baseUrl, documentLibraryName, dr, fullUrl);

                    //PDF BLOB UPLOAD
                    if (!DBNull.Value.Equals(dr["PDFBlob"]))
                    {
                        blob = (byte[])dr["PDFBlob"];
                        filExtension = ".PDF";
                        folderUrl = dr["PROTOCOLID"].ToString() + "/" + docID.ToString() + filExtension;
                        documentLibrary.SaveFileToSharePoint(folderUrl, blob, baseUrl + "/" + documentLibraryName);

                        bIsConvertedToPDF = true;
                    }
                }

            }
            catch (Exception ex)
            {
                bUploadedToSharePoint = false;
                string fileName = ConfigurationManager.AppSettings["ErrorLog"].ToString();
                System.IO.StreamWriter file = new System.IO.StreamWriter(fileName, true);
                file.WriteLine(ex.ToString() + Environment.NewLine + docID);

                file.Close();
            }
            MarkAsUploaded(docID, bUploadedToSharePoint, bIsConvertedToPDF, connection);
        }

        public void UpdateDocumentProperties(string url, string documentLibraryName, DataRow dr, string fullUrl)
        {

            Hashtable htMetaData = new Hashtable();
            htMetaData["ProcessName"] = dr["ProcessName"].ToString();
            htMetaData["DocName"] = dr["Name"].ToString();
            htMetaData["Att_Type"] = dr["Att_Title"].ToString();
            htMetaData["Att_Title"] = dr["Att_Title"].ToString();

            documentLibrary.UpdateMetadata(url, documentLibraryName, htMetaData, fullUrl);
        }

        public void UpdateProtocolProperties(DataRow dr,  string documentLibraryName)
        {
            string baseUrl = GetURL(dr);
            string fullUrl = baseUrl + "/" + documentLibraryName + "/" + dr["PROTOCOLID"].ToString();
            Hashtable htMetaData = new Hashtable();
            htMetaData["ProtocolNumber"] = dr["PROTOCOLNUMBER"].ToString();
            htMetaData["ProtocolTitle"] = dr["ShortTitle"].ToString();
            documentLibrary.UpdateMetadata(baseUrl, documentLibraryName, htMetaData, fullUrl);

        }

        public string GetURL(DataRow dr)
        {
            string moduleName = dr["ModuleType"].ToString() == "H" ? "Humans" : "Animals";
            string url = ConfigurationManager.AppSettings["SharePointBlobURL"];
            if (DBNull.Value.Equals(dr["PROTOCOLNUMBER"]))
            {
                url += "/" + moduleName + "/" + DateTime.Now.Year ;
            }
            else
            {

                url += "/" + moduleName +"/" +dr["PROTOCOLNUMBER"].ToString().Substring(0, 4);
            }
            return url;
        }
        /// <summary>
        /// Mark the uploaded flag as true
        /// </summary>
        /// <param name="docid"></param>
        /// <param name="bUploadedToSP"></param>
        /// <param name="IsConvertedToPDF"></param>
        /// <param name="connection"></param>
        public void MarkAsUploaded(int docid, bool bUploadedToSP, bool IsConvertedToPDF,
        SqlConnection connection)
        {

            SqlCommand command = new SqlCommand("dbo.UpdateStatusofUploadedDocument", connection);
            command.CommandType = CommandType.StoredProcedure;

            SqlParameter docId = new SqlParameter("@docid", DbType.Int32);
            docId.Value = docid;
            command.Parameters.Add(docId);

            SqlParameter pUploadedToSP = new SqlParameter("@UploadedToSP", SqlDbType.Bit);
            pUploadedToSP.Value = bUploadedToSP;
            command.Parameters.Add(pUploadedToSP);

            SqlParameter pIsConvertedToPDF = new SqlParameter("@IsConvertedToPDF", SqlDbType.Bit);
            pIsConvertedToPDF.Value = IsConvertedToPDF;
            command.Parameters.Add(pIsConvertedToPDF);
            command.ExecuteNonQuery();
        }
    }
}
